/* eslint-disable @typescript-eslint/no-require-imports */
import * as xlsx from 'xlsx';

interface ExcelToObjectOptions {
  headerKeyMap: { [headerStr: string]: string };
  headerRowCount?: number;
}
export type ExcelToObjectRes = {
  sheetName: string;
  data: any[];
};
function excelToObject(
  buffer: Buffer,
  options: ExcelToObjectOptions,
): ExcelToObjectRes[] {
  // 解析 Excel 文件
  const res = [];
  const workbook = xlsx.read(buffer, { type: 'buffer' });
  Object.entries(workbook.Sheets).forEach((v, i) => {
    const [sheetName, sheet] = v;
    const sheetData = getSheetData(sheet, options);
    res.push({
      sheetName,
      data: sheetData,
    });
  });
  return res;
}

const getSheetData = (
  worksheet: xlsx.WorkSheet,
  options: ExcelToObjectOptions,
): any[] => {
  // 获取合并单元格信息
  const merges = worksheet['!merges'] || [];

  // 获取表头行数，默认为 1
  const headerRowCount = options.headerRowCount || 1;

  // 将工作表转换为 JSON 数据
  const jsonData = xlsx.utils
    .sheet_to_json(worksheet, {
      header: 1,
      defval: '',
    })
    .filter((row: any[]) => {
      return row.find((v) => {
        return !!v;
      });
    });

  // 处理表头
  const headers = processHeaders(
    (jsonData as any).slice(0, headerRowCount),
    merges,
    options.headerKeyMap,
  );

  // 处理数据行
  const result = [];
  for (let i = headerRowCount; i < jsonData.length; i++) {
    const row = jsonData[i];
    const obj = {};
    let distObj = obj;
    headers.forEach((header, index) => {
      if (header) {
        const headerLength = header.length;
        header.forEach((v, i) => {
          if (headerLength === i + 1) {
            distObj[v] = row[index];
          } else {
            distObj[v] = distObj[v] || {};
            distObj = distObj[v];
          }
        });
      }
      distObj = obj;
    });
    result.push(obj);
  }

  return result;
};

function processHeaders(
  headerRows: any[][],
  merges: xlsx.Range[],
  headerKeyMap: { [headerStr: string]: string },
): string[][] {
  // 处理多行表头
  const headerGraph = [];
  for (let rowIndex = 0; rowIndex < headerRows.length; rowIndex++) {
    const row = headerRows[rowIndex];
    const headerKeys = [];
    row.forEach((cell, colIndex) => {
      if (!headerGraph[colIndex]) {
        headerGraph.push([]);
      }
      if (colIndex < headerKeys.length) {
        return;
      }
      if (cell) {
        const cellSpan = getCellSpan(merges, rowIndex, colIndex);
        if (cellSpan) {
          const [startCol, endCol] = cellSpan;
          const cellKey = headerKeyMap[cell] || cell;
          for (let i = startCol; i <= endCol; i++) {
            if (!headerGraph[i]) {
              headerGraph.push([]);
            }
            headerGraph[i].push(cellKey);
          }
        } else {
          const cellKey = headerKeyMap[cell] || cell;
          headerGraph[colIndex].push(cellKey);
        }
      } else {
        // headerKeys.push('');
      }
    });
  }
  return headerGraph;
}

function getCellSpan(
  merges: xlsx.Range[],
  rowIndex: number,
  colIndex: number,
): [number, number] | undefined {
  for (const merge of merges) {
    if (
      merge.s.r == rowIndex &&
      merge.s.c <= colIndex &&
      merge.e.c >= colIndex
    ) {
      return [merge.s.c, merge.e.c];
    }
  }
  return undefined;
}

export default excelToObject;

// 使用示例
// const buffer = fs.readFileSync(path.resolve(__dirname, '3333.xlsx'));
// const options: ExcelToObjectOptions = {
//   headerKeyMap: {
//     坑位: 'positionInfo',
//     团队名称: 'teamName',
//     选手: 'playerInfo',
//     项目: 'eventInfo',
//     组别: 'categoryInfo',
//     姓名: 'name',
//     性别: 'gender',
//     年龄: 'age',
//     身份证号: 'idCard',
//     电话: 'phone',
//   },
//   headerRowCount: 2,
// };
// const result = excelToObject(buffer, options);
// console.log(result);
